package com.cpi.basilos.size.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.log4j.Logger;

import com.cpi.basilos.size.exception.SizeException;
import com.cs.common.exception.CSException;
import com.cs.common.pool.DBDAO;

public class SizeDAO extends DBDAO {
	private static org.apache.log4j.Logger logger = Logger.getLogger(SizeDAO.class);
	private static final String SQL_GET_SIZE_BY_WAREHOUSE = "SELECT sum(cantidad) capacidad from VW_ALMACEN_TIPOGRANO_CAPACIDAD where idAlmacen = ? ";//$NON-NLS-1$
	private static final String SQL_GET_SIZE_BY_CORNTYPE = " and idTipoGrano = ? ";//$NON-NLS-1$
	private static final String SQL_GET_SIZE_BY_CORNTYPE_NULL = " and idTipoGrano is null ";//$NON-NLS-1$
	private static final String SQL_GET_SIZE_BY_LOCATION_NULL = " AND (infUbicacion IS NULL OR infUbicacion = '') ";//$NON-NLS-1$
	private static final String SQL_GET_SIZE_BY_LOCATION_NOT_NULL = " AND (infUbicacion IS NOT NULL AND infUbicacion <> '') ";//$NON-NLS-1$
	private static final String SQL_GET_SIZE_BY_LOCATION = " AND idUbicacion = ? ";//$NON-NLS-1$	
	public double totalSize( int warehouseId, int cornTypeId, boolean isLocation )throws SizeException{
		double size = 0;
		StringBuffer sql = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			sql = new StringBuffer(SQL_GET_SIZE_BY_WAREHOUSE.length() + SQL_GET_SIZE_BY_CORNTYPE_NULL.length() + SQL_GET_SIZE_BY_LOCATION_NOT_NULL.length());
			sql.append(SQL_GET_SIZE_BY_WAREHOUSE);
			sql.append(cornTypeId > 0 ? SQL_GET_SIZE_BY_CORNTYPE : SQL_GET_SIZE_BY_CORNTYPE_NULL);
			sql.append(isLocation ? SQL_GET_SIZE_BY_LOCATION_NOT_NULL : SQL_GET_SIZE_BY_LOCATION_NULL);
			preparedStatement = prepareSQL(sql.toString(), dsBasilos);
			preparedStatement.setInt(1, warehouseId);
			if( cornTypeId > 0 )preparedStatement.setInt(2, cornTypeId);
			resultSet = executeQuery(preparedStatement);
			if( resultSet.next() ){
				size = resultSet.getDouble(1);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			SizeException sizeException = null;
			logger.error(exception);
			exception.printStackTrace(System.out);
			sizeException = new SizeException(exception, CSException.LAYER_DAO, CSException.ACTION_SELECT);
			throw sizeException;
		}finally{
			try{
				connection.close();
			}catch(Exception sqlException){}
		}
		return size;
	}
	public double totalSizeByLocation(int warehouseId, int cornTypeId, boolean isLocation, String location)throws SizeException {
		double size = 0;
		StringBuffer sql = null;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try{
			sql = new StringBuffer(SQL_GET_SIZE_BY_WAREHOUSE.length() + SQL_GET_SIZE_BY_CORNTYPE_NULL.length() + SQL_GET_SIZE_BY_LOCATION_NOT_NULL.length() + SQL_GET_SIZE_BY_LOCATION.length());
			sql.append(SQL_GET_SIZE_BY_WAREHOUSE);
			sql.append(cornTypeId > 0 ? SQL_GET_SIZE_BY_CORNTYPE : SQL_GET_SIZE_BY_CORNTYPE_NULL);
			sql.append(isLocation ? SQL_GET_SIZE_BY_LOCATION_NOT_NULL : SQL_GET_SIZE_BY_LOCATION_NULL);
			sql.append(SQL_GET_SIZE_BY_LOCATION);
			preparedStatement = prepareSQL(sql.toString(), dsBasilos);
			preparedStatement.setInt(1, warehouseId);
			if( cornTypeId > 0 ){
				preparedStatement.setInt(2, cornTypeId);
				preparedStatement.setString(3, location);
			}else{
				preparedStatement.setString(2, location);
			}
			resultSet = executeQuery(preparedStatement);
			if( resultSet.next() ){
				size = resultSet.getDouble(1);
			}
			connection = preparedStatement.getConnection();
		}catch(Exception exception){
			SizeException sizeException = null;
			logger.error(exception);
			exception.printStackTrace(System.out);
			sizeException = new SizeException(exception, CSException.LAYER_DAO, CSException.ACTION_SELECT);
			throw sizeException;
		}finally{
			try{
				connection.close();
			}catch(Exception sqlException){}
		}
		return size;
	}
}